#!/usr/bin/env python
# -*- coding: utf-8 -*-

import pymysql
from db import config

class Model(object):

    tab_name = None #表名
    link = None #连接对象
    cursor = None #游标对象
    primery_key = '' #主键
    fileds = [] #字段
    unique_key = ''

    def __init__(self, tab_name):
        try:
            self.tab_name = tab_name
            self.link = pymysql.connect(host=config.HOST, port=config.PORT, user=config.USER, password=config.PASSWORD, db=config.DBNAME, charset='utf8')
            self.cursor = self.link.cursor(pymysql.cursors.DictCursor)
            self.__loadFields()
        except Exception as error:
            print('sql 连接失败%s' % error)

    def __loadFields(self):
        try:
            sql = 'SHOW COLUMNS FROM %s' % self.tab_name
            self.cursor.execute(sql)
            fields = self.cursor.fetchall()
            for v in fields:
                self.fileds.append(v['Field'])
                if v['Key'] == 'PRI':
                    self.primery_key = v['Field']
                if v['Key'] == 'UNI':
                    self.unique_key = v['Field']
        except Exception as error:
            self.fileds = []
            self.primery_key = ''
            print('获取表信息失败：%s' % error)

    def findAll(self):
        '''全部数据查询'''
        try:
            sql = 'select * from %s' % self.tab_name
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            return results
        except Exception as error:
            print('查询失败:%s' % error)
            return []

    def findOne(self, id=0):
        '''单条数据查询'''
        try:
            sql = 'select * from %s where %s=%s' % (self.tab_name, self.primery_key, str(id))
            self.cursor.execute(sql)
            results = self.cursor.fetchone()
            return results
        except Exception as error:
            print('查询失败:%s' % error)
            return None

    def select(self, where=[], order=None, limit=None):
        '''条件查询'''
        try:
            sql = 'select * from %s' % self.tab_name
            if isinstance(where, list) and len(where) > 0:
                sql += ' where ' + ' and '.join(where)
            if order is not None:
                sql += ' order by ' + order
            if limit is not None:
                sql += ' limit ' + str(limit)
            print(sql)
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            return results
        except Exception as error:
            print('查询失败:%s' % error)
            return []

    def insert(self, data={}):
        try:
            keys = []
            values = []
            for k,v in data.items():
                if k in self.fileds:
                    keys.append(k)
                    values.append(v)
            sql = 'insert into %s(%s) values (%s)' % (self.tab_name, ','.join(keys), ','.join(['%s']*len(values)))
            print(sql)
            self.cursor.execute(sql, tuple(values))
            self.link.commit()
            return self.cursor.lastrowid
        except Exception as error:
            print('数据插入失败：%s' % error)
            return 0

    def update(self, data={}):
        '''根据索引修改'''
        try:
            values = []
            for k, v in data.items():
                if (k in self.fileds) and (k != self.unique_key):
                    values.append("%s='%s'" % (k, v))
            sql = "update %s set %s where %s='%s'" % (self.tab_name, ','.join(values), self.unique_key, data.get(self.unique_key))
            print(sql)
            self.cursor.execute(sql)
            self.link.commit()
            return self.cursor.lastrowid
        except Exception as error:
            print('数据修改失败:%s' % error)
            return 0

    def delete(self, id=0):
        try:
            sql = 'delete from %s where %s=%s' % (self.tab_name, self.primery_key, id)
            self.cursor.execute(sql)
            self.link.commit()
            return self.cursor.lastrowid
        except Exception as error:
            print('数据删除失败:%s' % error)
            return 0

    def __del__(self):
        if self.cursor != None:
            self.cursor.close()
        if self.link != None:
            self.link.close()